import java.sql.*;

public class OscarJDBC8 {
    // 数据库配置（需替换为实际值）[[5, 19]]
    private static final String JDBC_URL = "jdbc:oscar://localhost:2003/test";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        // 1. 创建测试表
        createTable();

        // 2. 插入数据
        insertUser("张三", "zhangsan@example.com");

        // 3. 查询数据
        selectUsers();

        // 4. 更新数据
        updateUser("张三", "zhangsan_new@example.com");

        // 5. 删除数据
        deleteUser("张三");
    }

    // 创建表（仅首次执行）[[8]]
    private static void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT AUTO_INCREMENT PRIMARY KEY, " +
                "name VARCHAR(20) NOT NULL, " +
                "email VARCHAR(50))";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 插入操作（使用PreparedStatement防注入）[[2, 13]]
    private static void insertUser(String name, String email) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, name);
            ps.setString(2, email);
            int rows = ps.executeUpdate();  // 返回受影响行数[[10]]

            if (rows > 0) {
                System.out.println("插入成功！新用户: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询操作（遍历ResultSet）[[4, 16]]
    private static void selectUsers() {
        String sql = "SELECT * FROM users";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("\n当前用户列表：");
            while (rs.next()) {
                System.out.printf(
                        "ID: %d | 姓名: %s | 邮箱: %s\n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新操作（参数化查询）[[3, 9]]
    private static void updateUser(String name, String newEmail) {
        String sql = "UPDATE users SET email = ? WHERE name = ?";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, newEmail);
            ps.setString(2, name);
            int rows = ps.executeUpdate();

            if (rows > 0) {
                System.out.println("\n更新成功！用户: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除操作（Where条件）[[10, 13]]
    private static void deleteUser(String name) {
        String sql = "DELETE FROM users WHERE name = ?";
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, name);
            int rows = ps.executeUpdate();

            if (rows > 0) {
                System.out.println("\n删除成功！用户: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
